﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Mapeo;
using System.Data;
using NHibernate;
using MySql.Data.MySqlClient;
using BE;

namespace DAL
{
    public class NotaDesaprobados_DAL:Controlador
    {
        public DataTable ObtenerListadoNotasDesaprobadas(String codigo, String nombre)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_BusquenaNotasDesaprobadas_Alumno";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@inCodigo", codigo);
                    SqlCmd.Parameters.AddWithValue("@inNombre", nombre);
                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }  
        }


        public DataTable ObtenerPromedioAlumno(String codigo)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_PromediosAlumno";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@inCodigo", codigo);
                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }  
        }


        public Alumno obtenerAlumno(String Codigo)
        {
            return (Alumno)AyudaNHibernate.ObtenerEntidadPorID("Alumno", Codigo);
        }


        public Nota obtenerNota(String Codigo)
        {
            return (Nota)AyudaNHibernate.ObtenerEntidadPorID("Nota", Codigo);
        }


        public NotaComplementaria obtenerNotaComplementaria(String Codigo)
        {
            return (NotaComplementaria)AyudaNHibernate.ObtenerEntidadPorID("NotaComplementaria", "23");
        }

        public void InsertarNotaNueva(Nota nota, NotaComplementaria notac)
        {
            OperacionesSubnarNotasDesaprobadas ope = new OperacionesSubnarNotasDesaprobadas();
            ope.CrearNota(nota, notac);
            ope.ActualizarPeriodo(nota);
        }

        public Int32 CantidadDeVecesJalados(String CodigoAlumno, String CodigoEspecialidad, String CodigoCurso)
        {
            try
            {
                using (ISession Sesion = m_SessionFactory.OpenSession())
                {
                    using (ITransaction Trans = Sesion.BeginTransaction())
                    {
                        MySqlCommand SqlCmd = new MySqlCommand();
                        SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                        Trans.Enlist(SqlCmd);
                        SqlCmd.CommandText = "sp_ValidarCantidadDesaprobados";
                        SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                        SqlCmd.Parameters.AddWithValue("@inCodAlumno", CodigoAlumno);
                        SqlCmd.Parameters.AddWithValue("@inEspecialidad", CodigoEspecialidad);
                        SqlCmd.Parameters.AddWithValue("@inCurso", CodigoCurso);
                        SqlCmd.Parameters.Add("@outCantidaVeces", MySqlDbType.Int32, 50);
                        SqlCmd.Parameters["@outCantidaVeces"].Direction = ParameterDirection.Output;
                        SqlCmd.ExecuteNonQuery();
                        Trans.Commit();
                        return Convert.ToInt32(SqlCmd.Parameters["@outCantidaVeces"].Value);
                    }
                }
            }
            catch (Exception ex)
            {
                new Exception(ex.Message);
                return 0;
            }
        }




    }
}
